package su.jet.irc.datagateway;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.Map;
import java.util.Set;

import su.jet.common.irc.domain.Message;
import su.jet.common.irc.domain.User;

public class DataAccess {
	private Connection connection;
	
	public DataAccess(String dbName) throws SQLException {
		connection = DriverManager.getConnection(
				"jdbc:derby:res/" + dbName + ";create=false");
		
	}
	public void createNewTable() throws SQLException {
		String sql = "CREATE TABLE messages (" +
				"id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
				"sender_id int," +
				"receiver_id int," +
				"channel_id int," +
				"is_public boolean," +
				"message_text varchar(255)," +
				"posting_date timestamp)";
		
		Statement statement = connection.createStatement();
		statement.executeUpdate(sql);
	}
	
	public void deleteTable() throws SQLException {
		String sql = "DROP TABLE messages";
		Statement statement = connection.createStatement();
		statement.executeUpdate(sql);
	}
	
	public Set<User> getUsers() throws SQLException {
		User user;
		Set<User> users = new HashSet<User>();
		Statement selectUsers = connection.createStatement();
		ResultSet result = selectUsers.executeQuery(
				"SELECT nickName, password FROM users");
		while(result.next()) {
			user = new User (result.getString(1).trim(),
					result.getString(2).trim());
			users.add(user);
		}
		return users;
	}
	
	public void addUser(User user) throws SQLException {
		PreparedStatement addUser = connection.prepareStatement("INSERT INTO users (nickname, password) VALUES (?, ?)");
		addUser.setString(1, user.getNickName());
		addUser.setString(2, user.getPassword());
		addUser.executeUpdate();
	}
	
	public void addMessage(Message message, Timestamp timestamp) {
		User sender = message.getSender();
		User receiver = message.getReceiver();
		String messageText = message.getText();
		boolean isPublicMessage = (receiver == null);
		
		try {
			int senderId = getUserIdByNickName(sender.getNickName());
			int receiverId = 0;
			if (!isPublicMessage)
				receiverId = getUserIdByNickName(receiver.getNickName());
			
			String sql = "INSERT INTO messages (sender_id, receiver_id, is_public, message_text, posting_date) " +
					"VALUES (?, ?, ?, ?, ?)";
			PreparedStatement addMessage = connection.prepareStatement(sql);
			addMessage.setInt(1, senderId);
			addMessage.setInt(2, receiverId);
			addMessage.setBoolean(3, isPublicMessage);
			addMessage.setString(4, messageText);
			addMessage.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
						
			addMessage.executeUpdate();
		} catch (SQLException e) {
			System.out.println("sql error");
			e.printStackTrace();
		}
	}
	
	/*public String getMessage(int id) throws SQLException {
		String sql = "SELECT sender_id, receiver_id, message_text, posting_date FROM messages WHERE id = ?";
		PreparedStatement cmd = connection.prepareStatement(sql);
		cmd.setInt(1, id);
		
		String message = "";
		ResultSet result = cmd.executeQuery();
		while (result.next()) {
			message += result.getInt(1) + " ";
			message += result.getInt(2) + " ";
			message += result.getString(3) + " ";
			message += result.getString(4) + " ";
		}
		return message;
	}*/
	
	public Map<Message, Timestamp> getMessagesFromPeriod(User user, int period) throws SQLException {
		long k = 24 * 60 * 60 * 1000;
		Map<Message, Timestamp> messagesMap = new  Hashtable<Message, Timestamp>();
		Date beginPeriodDate = new Date(System.currentTimeMillis() - period * k);
		
		String sql = "SELECT sender_id, receiver_id, message_text, posting_date FROM messages WHERE posting_date > ? AND " +
				"(receiver_id = 0 OR receiver_id = ? OR sender_id = ?) ORDER BY posting_date DESC";
		PreparedStatement cmd = connection.prepareStatement(sql);
		cmd.setDate(1, beginPeriodDate);
		cmd.setInt(2, getUserIdByNickName(user.getNickName()));
		cmd.setInt(3, getUserIdByNickName(user.getNickName()));
		
		ResultSet result = cmd.executeQuery();
		while (result.next()){
			User receiver = null;
			if (result.getInt(2) != 0)
				receiver = new User(getNickNameById(result.getInt(2)));
			
			Message message = new Message(result.getString(3),
					new User(getNickNameById(result.getInt(1))),
					receiver);
			
			messagesMap.put(message, result.getTimestamp(4));
		}
		return messagesMap;
	}
			
	public String getNickNameById(int id) throws SQLException {
		String sqlGetUserNickName = "SELECT nickname FROM users WHERE userid = ?";
		PreparedStatement getUserId = connection.prepareStatement(sqlGetUserNickName);
		getUserId.setInt(1, id);
		ResultSet result = getUserId.executeQuery();
		result.next();
		return result.getString(1);
	}
	
	public int getUserIdByNickName(String nickname) throws SQLException {
		String sqlGetUserId = "SELECT userid FROM users WHERE nickname = ?";
		PreparedStatement getUserId = connection.prepareStatement(sqlGetUserId);
		getUserId.setString(1, nickname);
		
		ResultSet result = getUserId.executeQuery();
		result.next();
		return Integer.parseInt(result.getString(1));
	}
}